<?php
/**账单资金模型管理*/
namespace App\Http\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Bill extends Model
{
    protected $table = 'bill';

    /**
     * 按账单月份排序
     * @res_num 所有符合条件的数据
     * @res_page 分页查询符合条件的数据
     */
    public static function bill_months($param)
    {
        //查询字段名
        $sql_name=' DATE_FORMAT(`months_bill`,"%Y-%m") months,sum(`amount`) as amounts ';
        //查询条件
        $sql_where = ' userid='.$param['userid'].' group by DATE_FORMAT(`months_bill`,"%Y-%m") ';
        //排序
        $sql_order = ' ORDER BY months_bill ASC';
        //查询符合条件的所有数据
        $res['res_num'] = DB::select('SELECT '.$sql_name.' FROM mzj_bill WHERE '.$sql_where.$sql_order);
        if(isset($param['pageNow']) && $param['pageNow']!=''){
            $offset = ' OFFSET '.($param['pageNow']-1)*$param['per_page'];//偏移量
        }else{
            $res['month_now_desc'] = DB::select('SELECT '.$sql_name.' FROM mzj_bill WHERE DATE_FORMAT(`months_bill`,"%Y-%m") <= '."'".$param['month_time']."'".' AND '.$sql_where.$sql_order);
            $param['pageNow']=ceil(count($res['month_now_desc'])/$param['per_page']);
            if( $param['pageNow']<1){
                $param['pageNow']=1;
            }
            $offset = ' OFFSET '.($param['pageNow']-1)*$param['per_page'];
        }
        //分页数据
        $sql_limit = ' LIMIT '.$param['per_page'].$offset;
        $res['res_page']= DB::select('SELECT '.$sql_name.' FROM mzj_bill WHERE '.$sql_where.$sql_order.$sql_limit);
        $res['pageNum'] = ceil(count($res['res_num'])/$param['per_page']);
        $res['pageNow'] = $param['pageNow'];
        return $res;
    }
    /**账单状态【未付款与已付款状态下的数据统计】*/
    public static function bill_months_status($userid,$status,$months_bill)
    {
        $res = DB::select('select sum(`amount`) as amounts from mzj_bill where `userid`='.$userid.' AND `status`='.$status.' AND DATE_FORMAT(months_bill,"%Y-%m")='."'".$months_bill."'");
        return $res;
    }
    /**
     * 每月账单数据
     * @param['userid']用户id
     */
    public static function bill_month_list($param)
    {
        $res = DB::table('bill')
            ->leftJoin('order_details as od_dt','od_dt.id','=','bill.orderdtid')
            ->leftJoin('fitment_details as fit_dt','fit_dt.details_id','=','od_dt.details_fitment_id')
            ->leftJoin('fitment_color as color','color.color_details_id','=','fit_dt.details_id')
            ->leftJoin('fitments as fit','fit.fitments_id','=','fit_dt.details_pid')
            ->where('bill.userid',$param['userid'])
            ->where('bill.months_bill','like','%'.$param['months_bill'].'%')
            ->select(
                'bill.id','bill.num','bill.price','bill.amount','bill.fitment_dt_img','bill.rent_type','bill.status','bill.months_bill','bill.rent_start_date',
                'od_dt.details_fitment_start','od_dt.details_fitment_id',
                'fit_dt.details_name','fit_dt.details_length','fit_dt.details_width','fit_dt.details_describe',
                'color.color_type',
                'fit.fitments_name','fit.fitments_describe'
            )
            ->paginate($param['per_page']);
        return $res;
    }
    /**账单列表所有数据(暂时不删)*/
    public static function bill_list($request)
    {
        $res = DB::table('bill as bl')
            ->leftJoin('order_details as dt','dt.id','=','bl.orderdtid')
            ->leftJoin('fitment_details as ft_dt','ft_dt.details_id','=','dt.details_fitment_id')
            ->leftJoin('fitments as ft','ft.fitments_id','=','ft_dt.details_pid')
            ->leftJoin('details_img as img','img.fitment_details_id','=','ft_dt.details_id')
            ->leftJoin('fitment_color as color','color.color_details_id','=','ft_dt.details_id')
            ->where('bl.isdel',1);
        if(isset($request['userid']) && $request['userid'] != ''){
            $res = $res->where('bl.userid',$request['userid']);
        }
        if(isset($request['months_bill']) && $request['months_bill'] != ''){
            $res = $res->where('bl.months_bill','like','%'.$request['months_bill'].'%');
        }
        if(isset($request['status']) && $request['status'] != ''){
            $res = $res->where('bl.status',$request['status']);
        }
        $res = $res
            ->select('bl.*','ft.fitments_name','ft_dt.details_id','img.fitment_details_imgMaster','img.fitment_details_imgOne','color.color_type')
            ->orderBy('rent_start_date','desc')
            ->paginate($request['per_page'],['*'],'page',$request['current_page']);
        return $res;
    }
    
    /**
     * 系统账单账单生成（执行月初生成账单）
     * 查询可生成账单
     * 避免数据重复,查询校验,设定数据状态更新，采取替换机制
     * 校验如果已有下月账单，为防止更新后的状态影响用户查看，采用替换
     */
    public static function bill_create()
    {
        /**查询是否有订单需要生成账单*/
        $res = DB::select('SELECT od.`orders_user_id`,dt.`id`,dt.`details_rent_type`,dt.`details_fitment_start`,dt.`details_fitment_end`,dt.`details_fitment_number`,dt.`rent_num`,dt.`repair_num`,dt.`sublet_num`,dt.`outrent_num`,fit_dt.`details_fixprice`,fit_dt.`details_nimbleprice`,img.`fitment_details_imgOne` FROM mzj_orders od LEFT JOIN mzj_order_details dt ON od.`id`=dt.`details_order_id` JOIN mzj_details_img img ON dt.`details_fitment_id`=img.`fitment_details_id` JOIN mzj_fitment_details fit_dt ON dt.`details_fitment_id`=fit_dt.`details_id` where dt.`details_orders_isdel`=1 AND dt.`details_status`=1 AND od.`orders_status`>0 AND date_format(dt.`details_fitment_end`,"%Y-%m")>='.date('Y-m',strtotime('+1 months')));
        /**查询系统已生成的下月账单，查询准备删除执行重新生成轮替*/
        $bill = DB::select('SELECT * FROM mzj_bill WHERE DATE_FORMAT(`months_bill`,"%Y-%m")='.date('Y-m',strtotime('+1 months')));

        if(count($bill)>0){
            $de = DB::table('DELETE FROM mzj_bill WHERE DATE_FORMAT(months_bill,"%Y-%m")='.date('Y-m',strtotime('+1 months')));
            if(!$de){
                return '删除老账单失败!';
            }
        }

        if(count($res)<1){
            $log = [
                'userid'=>0,'action'=>'/Bill',
                'detail'=>'暂无可生成账单数据!','status'=>0,
                'created_at'=>date('Y-m-d H:i:s',time()),
                'updated_at'=>date('Y-m-d H:i:s',time())
            ];
            Log::insertlog($log);
            return '暂无可生成账单数据!';
        }

        foreach ($res as $k=>$v)
        {
            $data[$k] = [
                'userid'=>$v->orders_user_id,
                'fitment_dt_img'=>$v->fitment_details_imgOne,
                'orderdtid'=>$v->id,
                'rent_type'=>$v->details_rent_type,
                'num'=> ($v->rent_num+$v->repair_num),
                'status'=>0,
                'months_bill'=>date('Y-m-d',strtotime('+1 months')),
                'rent_start_date'=>$v->details_fitment_start,
                'rent_end_date'=>$v->details_fitment_end,
                'isdel'=>1,
                'created_at'=>date('Y-m-d H:i:s',time()),
                'updated_at'=>date('Y-m-d H:i:s',time())
            ];
            if($v->details_rent_type==1){
                $data[$k]['amount'] = $v->details_nimbleprice*($v->rent_num+$v->repair_num);
                $data[$k]['price'] = $v->details_nimbleprice;
            }else{
                $data[$k]['amount'] = $v->details_fixprice*($v->rent_num+$v->repair_num);
                $data[$k]['price'] = $v->details_fixprice;
            }
            $insert = DB::table('bill')->insertGetId($data[$k]);
            if(!$insert)
            {
                $log = [
                    'userid'=>$v->orders_user_id,'action'=>'/Bill',
                    'detail'=>'系统生成账单,账单详情'.$data[$k]['orderdtid'].'时出错!','status'=>0,
                    'created_at'=>date('Y-m-d H:i:s',time()),
                    'updated_at'=>date('Y-m-d H:i:s',time())
                ];
                Log::insertlog($log);
                $result[$k] = ['res'=>'error','msg'=>'系统生成账单,账单详情'.$data[$k]['orderdtid'].'时出错!'];
            }
            $result=['res'=>'ok','msg'=>'生成账单'.date('Y-m',strtotime('+1 months'))];
        }
        return $result;
    }

    /**
     * 新增订单过来账单,供支付订单完成后调用!（若失败，需要等系统定时生成补上）
     * 当月账单付清计算
     * @orderid
     */
    public static function bill_order($orderid)
    {
        /**新加账单数据*/
        $res = DB::table('orders as od')
            ->leftjoin('order_details as dt','od.id','=','dt.details_order_id')
            ->join('fitment_details as fit_dt','dt.details_fitment_id','=','fit_dt.details_id')
            ->join('details_img as img','dt.details_fitment_id','=','img.fitment_details_id')
            ->where('od.id',$orderid)
            ->where('dt.details_orders_isdel',1)
            ->where('od.orders_status','>',0)
            ->select(
                'od.orders_user_id',
                'fit_dt.details_fixprice','fit_dt.details_nimbleprice',
                'dt.id','dt.details_rent_type','dt.details_fitment_start','dt.details_fitment_end','dt.details_fitment_number',
                'img.fitment_details_imgOne'
            )->get();

        DB::beginTransaction();
        try{
            if(count($res)>0){
                foreach($res as $v)
                {
                    $insertdata = [
                        'userid' => $v->orders_user_id,
                        'fitment_dt_img' => $v->fitment_details_imgOne,
                        'orderdtid' => $v->id,
                        'rent_type' => $v->details_rent_type,
                        'num' => $v->details_fitment_number,
                        'status' => 1,
                        'rent_start_date' => $v->details_fitment_start,
                        'rent_end_date' => $v->details_fitment_end,
                        'months_bill' => date('Y-m-d',time()),
                        'pay_bill_time' => date('Y-m-d H:i:s',time()),
                        'isdel' => 1,
                        'created_at' => date('Y-m-d H:i:s',time()),
                        'updated_at' => date('Y-m-d H:i:s',time())
                    ];
                    if($v->details_rent_type==1){
                        $insertdata['price'] = $v->details_nimbleprice;
                        $insertdata['amount'] = $v->details_nimbleprice*$v->details_fitment_number;
                    }else{
                        $insertdata['price'] = $v->details_fixprice;
                        $insertdata['amount'] = $v->details_fixprice*$v->details_fitment_number;
                    }
                    $insertbill = DB::table('bill')->insertGetId($insertdata);
                    if(!$insertbill) {
                        DB::rollback();
                        return '查询成功,在生成订单当前账单时出错!';
                    }
                    $insertnextdata = $insertdata;
                    $insertnextdata['status'] = '0';
                    $insertnextdata['pay_bill_time'] = null;
                    $insertnextdata['months_bill'] = date('Y-m-d',strtotime('+1 months'));
                    $insertnext = DB::table('bill')->insertGetId($insertnextdata);

                    if(!$insertnext){
                        DB::rollback();
                        return '在生成下月账单时出错!';
                    }
                }
            }else{
                DB::rollback();
                return '没有查询到订单数据，没法生成账单!';
            }
            DB::commit();
            return 'ok';
        }catch(Exception $e){
            DB::rollback();
            return '生成账单失败!';
        }
    }

    /**根据订单详情id查询该在租详情近期账单*/
    public static function bill_orderdt($orderdtid)
    {
        $res = DB::table('bill as bl')
            ->leftJoin('order_details as or_dt','or_dt.id','=','bl.orderdtid')
            ->where('bl.orderdtid',$orderdtid)
            ->where(function($query){
                $query->where('bl.months_bill','<=',date('Y-m-d',time()))
                    ->orwhere('bl.months_bill','like','%'.date('Y-m',time()).'%');
            })
            ->where('bl.status',0)
            ->select(
                'bl.id','bl.userid','bl.price','bl.num','bl.amount','bl.status','bl.months_bill',
                'bl.rent_start_date',
                'or_dt.details_orderNo','or_dt.details_status','or_dt.details_fitment_number',
                'or_dt.rent_num','or_dt.repair_num','or_dt.sublet_num','or_dt.outrent_num',
                'or_dt.details_fitment_id','or_dt.details_fitment_start','or_dt.details_fitment_end'
            )
            ->get();
        return $res;
    }

    /**根据订单统计近期该商品账单欠款总额*/
    public static function bill_orderdtsum($orderdtid)
    {
        $res = DB::table('bill as bl')
            ->leftJoin('order_details as or_dt','or_dt.id','=','bl.orderdtid')
            ->where('bl.orderdtid',$orderdtid)
            ->where(function($query){
                $query->where('bl.months_bill','<=',date('Y-m-d',time()))
                    ->orwhere('bl.months_bill','like','%'.date('Y-m',time()).'%');
            })
            ->where('bl.status',0)
            ->sum('bl.amount');
        return $res;
    }

    public static function test(){
        $log = [
            'userid'=>0,'action'=>'/Bill',
            'detail'=>'这是定时测试!','status'=>0,
            'created_at'=>date('Y-m-d H:i:s',time()),
            'updated_at'=>date('Y-m-d H:i:s',time())
        ];
        Log::insertlog($log);
        return '这是定时测试';
    }

    //用户当月需付账单
    public static function bill($param)
    {
        //查询字段名
        $sql_name=' bill.`id`,bill.`amount`,bill.`price`,bill.`rent_type`,bill.`num`,bill.`months_bill`,bill.`created_at`,bill.`status`,ft.`fitments_name`,ft_dt.`details_name`,ft_dt.`details_describe`,ft_dt.`details_width`,ft_dt.`details_length`,img.`fitment_details_imgMaster`,color.`color_type`,ft_dt.`details_fixprice`,ft_dt.`details_nimbleprice` ';
        //查询关联表
        $sql_tables = ' LEFT JOIN mzj_order_details AS od_dt ON bill.`orderdtid`=od_dt.`id` LEFT JOIN mzj_fitment_details AS ft_dt ON ft_dt.`details_id`=od_dt.`details_fitment_id` LEFT JOIN mzj_fitments AS ft ON ft.`fitments_id`=ft_dt.`details_pid` LEFT JOIN mzj_details_img AS img ON img.`fitment_details_id`=ft_dt.`details_id` LEFT JOIN mzj_fitment_color AS color ON color.`color_details_id`=ft_dt.`details_id` ';
        //查询条件
        $sql_where = ' WHERE bill.userid='.$param['userid'].' AND bill.`status`=0 AND '. "DATE_FORMAT(bill.`months_bill`,'%Y-%m')" .' LIKE '."'%".date('Y-m')."%'";
        //排序
        $sql_orderby = ' ORDER BY bill.`created_at` DESC ';
        //满足条件的数据
        $res_count = DB::select('SELECT count(bill.`id`) AS `num` FROM mzj_bill AS bill '.$sql_tables.$sql_where);
        //满足条件分页数据
        $sql_limit = ' LIMIT '.$param['per_page'];
        if(isset($param['offset']) && $param['offset']!=''){
            $sql_offset = ' OFFSET '.$param['offset'];
        }else{
            $sql_offset = '';
        }
        $res_page = DB::select('SELECT '.$sql_name.' FROM mzj_bill AS bill '.$sql_tables.$sql_where.$sql_orderby.$sql_limit.$sql_offset);
        $result = ['res_count'=>$res_count,'res_page'=>$res_page];
        return $result;
    }

}
